package org.example.managementsystem.repository;

import org.example.managementsystem.model.Provider;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
import java.util.Objects;

/**
 * 供应商数据访问层
 */
@Repository
public class ProviderDao {
    private final JdbcTemplate jdbcTemplate;

    @Autowired
    public ProviderDao(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    /**
     * 添加供应商
     */
    /**
     * 添加供应商
     */
    public void insertProvider(Provider provider) {
        String sql = "INSERT INTO provider (provider_name, factory_address, manager_name, " +
                "manager_phone, company_type, registered_capital, employee_count, " +
                "established_year, profile, provider_rating) " +
                "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
        
        jdbcTemplate.update(sql,
                provider.getProviderName(),
                provider.getFactoryAddress(),
                provider.getManagerName(),
                provider.getManagerPhone(),
                provider.getCompanyType(),
                provider.getRegisteredCapital() != null ? provider.getRegisteredCapital() : 0,
                provider.getEmployeeCount() != null ? provider.getEmployeeCount() : 0,
                provider.getEstablishedYear() != null ? provider.getEstablishedYear() : 0,
                provider.getProfile(),
                provider.getProviderRating() != null ? provider.getProviderRating() : 0);
    }

    /**
     * 更新供应商信息
     */
    public int updateProvider(Provider provider) {
        String sql = "UPDATE provider SET factory_address = ?, manager_name = ?, " +
                "manager_phone = ?, company_type = ?, registered_capital = ?, " +
                "employee_count = ?, established_year = ?, profile = ?, " +
                "provider_rating = ? WHERE provider_name = ?";
        
        return jdbcTemplate.update(sql,
                provider.getFactoryAddress(),
                provider.getManagerName(),
                provider.getManagerPhone(),
                provider.getCompanyType(),
                provider.getRegisteredCapital(),
                provider.getEmployeeCount(),
                provider.getEstablishedYear(),
                provider.getProfile(),
                provider.getProviderRating(),
                provider.getProviderName());
    }

    /**
     * 删除供应商
     */
    public int deleteProvider(String providerName) {
        String sql = "DELETE FROM provider WHERE provider_name = ?";
        return jdbcTemplate.update(sql, providerName);
    }

    /**
     * 根据供应商名称查询供应商
     */
    public Provider getProviderByName(String providerName) {
        String sql = "SELECT * FROM provider WHERE provider_name = ?";
        List<Provider> providers = jdbcTemplate.query(sql, new ProviderRowMapper(), providerName);
        return providers.isEmpty() ? null : providers.get(0);
    }

    /**
     * 查询所有供应商
     */
    public List<Provider> getAllProviders() {
        String sql = "SELECT * FROM provider";
        return jdbcTemplate.query(sql, new ProviderRowMapper());
    }

    /**
     * 搜索供应商
     */
    public List<Provider> searchProviders(String keyword) {
        String sql = "SELECT * FROM provider WHERE provider_name LIKE ? " +
                "OR factory_address LIKE ? OR manager_name LIKE ? OR manager_phone LIKE ? " +
                "OR company_type LIKE ?";
        
        String searchParam = "%" + keyword + "%";
        return jdbcTemplate.query(sql, new ProviderRowMapper(), 
                searchParam, searchParam, searchParam, searchParam, searchParam);
    }

    /**
     * 按评级搜索供应商
     */
    public List<Provider> searchProvidersByRating(Integer rating) {
        String sql = "SELECT * FROM provider WHERE provider_rating = ?";
        return jdbcTemplate.query(sql, new ProviderRowMapper(), rating);
    }
    /**
     * 供应商行映射器
     */
    private static class ProviderRowMapper implements RowMapper<Provider> {
        @Override
        public Provider mapRow(ResultSet rs, int rowNum) throws SQLException {
            Provider provider = new Provider();
            provider.setProviderName(rs.getString("provider_name"));
            provider.setFactoryAddress(rs.getString("factory_address"));
            provider.setManagerName(rs.getString("manager_name"));
            provider.setManagerPhone(rs.getString("manager_phone"));
            provider.setCompanyType(rs.getString("company_type"));
            provider.setRegisteredCapital(rs.getDouble("registered_capital"));
            provider.setEmployeeCount(rs.getInt("employee_count"));
            provider.setEstablishedYear(rs.getInt("established_year"));
            provider.setProfile(rs.getString("profile"));
            provider.setProviderRating(rs.getInt("provider_rating"));
            
            return provider;
        }
    }
}
